First, we'll connect to the niPOD database.
Note: if you have the 32 bit version of ACE installed, you must use a 32 bit python to do this. See http://stackoverflow.com/questions/25820698/how-do-i-import-an-accdb-file-into-python-and-use-the-data
In [1]:
import pyodbc
driver = '{Microsoft Access Driver (*.mdb, *.accdb)}'
db_path = 'C:\\niPOD\\Database\\niPOD.accdb'
conn = pyodbc.connect('DRIVER={};DBQ={}'.format(driver,db_path))
cursor = conn.cursor()
Tada!
OK, now let's see what interesting tables we have.
In [2]:
table_list = []
for row in cursor.tables():
if 'SYSTEM' not in row[3]:
table_list.append(row[2])
print 'Tables:'
print ' ' +'\n '.join(table_list)
In [3]:
for table in table_list:
cursor.execute("select * from [%s]" % table)
columns = [column[0] for column in cursor.description]
n = len(list(cursor))
print "%s (%i rows)" % (table,n)
print ' ' +'\n '.join(columns)
So a few of these are simply lookup tables. Many are empty. ProbeSpec, ProbePackage, and Mapping are the big ones. I'm guessing those have what we want.
Let's write up a little function to query them and drop the rows into a pandas dataframe.
In [4]:
import pandas as pd
def get_df(table,cursor):
cursor.execute("select * from [%s]" % table)
columns = [column[0] for column in cursor.description]
results = []
for row in cursor.fetchall():
results.append(dict(zip(columns, row)))
return pd.DataFrame(results)
OK, let's see what's in one of these tables now.
In [5]:
df = get_df('ProbeSpec',cursor)
print len(df)
df.head()
Out[5]:
That looks like what we want. 1440 rows with DesignName as one of the columns and a bunch of data on the number of channels, shanks, etc.
Let's pull all of the non-empty tables down and drop them into CSV.
In [6]:
for table in table_list:
df = get_df(table,cursor)
if len(df)>0:
df.to_csv('NiPOD-%s.csv' % table,
encoding='utf-8',
index=False)
In [7]:
%ls *.csv
Beautiful. Next, we'll see if we can build KlustaKwik geometries from the data in this database.